* Prepare Store Locations geographic data

* Store locations

* Note: company names have been anonymized. Contact authors upon gaining access to the data for original company names.

	* Company 1
	clear
	gen Latitude = .
	gen Longitude = .
	tempfile full
	save `full', replace
	foreach month in "10-01" "09-01" "08-01" "07-01" "06-01" "05-01" "02-28" "01-31" {
		foreach sheet in 1 10 {
			import excel "$data/[Company 1 Data]", ///
				clear sheet("`sheet'") firstrow cellrange(A5)
			keep if FacilityOpen == "Y"
			keep if Country == "USA"
			drop if State == "Alaska" | State == "Hawaii"
			keep Latitude Longitude
			gen retail = 0
			if `sheet' > 1 {
				replace retail = 1
			}
			append using `full'
			save `full', replace
		}
	}
	duplicates drop
	drop if Latitude == .
	rename Latitude latitude
	rename Longitude longitude
	gen company = 70129
	save "$data/store_locations/output/11111_locations_2023", replace
	
	* Company 2
	clear
	gen Latitude = .
	gen Longitude = .
	tempfile full
	save `full', replace
	foreach month in "April" "May" "June" "July" "August" "September" "October" {
		foreach sheet in 1 8 {
			import excel "$data/[Company 2 Data]", ///
				clear sheet("`sheet'") firstrow cellrange(A5)
			keep if Open == "Y"
		keep if Country == "USA"
		drop if State == "Alaska" | State == "Hawaii"
		keep Latitude Longitude
		gen retail = 0
		if `sheet' > 1 {
			replace retail = 1
		}
			append using `full', force
			save `full', replace
		}
	}
	duplicates drop
	drop if Latitude == .
	rename Latitude latitude
	rename Longitude longitude
	gen company = 12250
	destring latitude longitude, force replace
	save "$data/store_locations/output/33333_locations_2023", replace

	* Company 3
	clear
	gen Latitude = .
	gen Longitude = .
	tempfile full
	save `full', replace
	foreach sheet in 1 7 {
		local range 5
		if `sheet' == 7 {
			local range 4
		}
		import excel "$data/[Company 3 Data 1]", ///
			clear sheet("`sheet'") firstrow cellrange(A`range')
		keep if Open == "Y"
		keep if Country == "USA"
		drop if State == "Alaska" | State == "Hawaii"
		keep Latitude Longitude
		gen retail = 0
		if `sheet' > 1 {
			replace retail = 1
		}
		append using `full', force
		save `full', replace
		}
	foreach month in "May" "June" "July" "August" "September" "October" {
		foreach sheet in 1 8 {
			import excel "$data/[Company 3 Data 2]", ///
				clear sheet("`sheet'") firstrow cellrange(A5)
			keep if Open == "Y"
			keep if Country == "USA"
			drop if State == "Alaska" | State == "Hawaii"
			keep Latitude Longitude
			gen retail = 0
			if `sheet' > 1 {
				replace retail = 1
			}
			append using `full', force
			save `full', replace
		}
	}
	duplicates drop
	drop if Latitude == .
	rename Latitude latitude
	rename Longitude longitude
	gen company = 10108
	destring latitude longitude, force replace
	save "$data/store_locations/output/22222_locations_2023", replace

* CZ shapefile to Stata DTA
shp2dta using "$data/crosswalks/input/cz1990_shapefile/cz1990.shp", database("$data/crosswalks/cz1990_db") coordinates("$data/crosswalks/cz1990_coord")
